package com.ruoyi.common.utils.poi;

import com.ruoyi.common.annotation.Excel;
import com.ruoyi.common.config.RuoYiConfig;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.core.text.Convert;
import com.ruoyi.common.exception.BusinessException;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * 工具类
 * created on 2021/5/20
 */
public class ExcelUtils<T>
{
    private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class);

    /**
     * 工作表名称
     */
    private String sheetName;
    /**
     * 工作薄对象
     */
    private Workbook workbook;

    /**
     * 工作表对象
     */
    private Sheet sheet;
    /**
     * 实体对象
     */
    public Class<T> clazz;

    public ExcelUtils(Class<T> clazz) {
        this.clazz = clazz;
    }

    /**
     * 样式列表
     */
    private Map<String, CellStyle> styles;
    /**
     * 导入导出数据列表
     */
    private List<T> list;

    private Excel.Type type;

    /**
     * 标题
     */
    private String excelTitle;

    private static final String SETTER_PREFIX = "set";

    private static final String GETTER_PREFIX = "get";


    /**
     * 初始化数据
     *
     * @param list
     * @param sheetName
     */
    public void init(List<T> list, String sheetName) {
        if (list == null) {
            // 设置空集合
            list = new ArrayList<>();
        }
        this.sheetName = sheetName;
        this.list = list;
        if (this.excelTitle == null) {
            this.excelTitle = System.currentTimeMillis() + sheetName;
        }
        createWorkbook();
    }

    /**
     * 生成excel公共方法
     *
     * @param list
     * @param fields
     * @param excelTitle
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     */
    public void entityMethod(List<T> list, Map<String, String> fields, String excelTitle) throws InvocationTargetException, IllegalAccessException {
        this.type = Excel.Type.EXPORT;
        this.init(list, excelTitle);

        createSheet(0, 0);

        // 创建列头行
        Row rowColumn = sheet.createRow(0);
        int count = 0;

        // 将列头设置到sheet的单元格中
        for (String key : fields.keySet()) {
            XSSFRichTextString text = new XSSFRichTextString(key);
            createCell(rowColumn, count, text);
            sheet.setColumnWidth(count, 6000);
            count++;
        }

        if (list != null) {
            // 遍历集合数据，产生数据行
            for (int i = 0; i < list.size(); i++) {
                //反射获取对象的属性
                T t = list.get(i);
                // 创建所需的行数
                Row row = sheet.createRow(i + 1);
                int index = 0;

                for (String key : fields.keySet()) {
                    String fieldName = fields.get(key);
                    Object value = getValue(t, fieldName, null);
                    addCell(row, index, value);
                    index++;
                }
            }
        }
    }

    /**
     * 导出数据 (模板下载公用此方法，list传 null)
     *
     * @param fields        头部列
     * @param dataList   行数据
     * @param excelTitle 导出文件名称
     */
    public AjaxResult export(Map<String, String> fields, List<T> dataList, String excelTitle) {
        try {
            entityMethod(dataList, fields, excelTitle);
            String filename = this.excelTitle;
            OutputStream out = new FileOutputStream(getAbsoluteFile(filename));
            workbook.write(out);
            log.info("导出成功" + filename);
            return AjaxResult.successMsg(filename);
        } catch (Exception e) {
        	log.error("导出Excel异常{}", e.getMessage());
            throw new BusinessException("导出Excel失败，请联系网站管理员！");
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public AjaxResult export(Map<String, String> fields, List<T> dataList) {
        String excelTitle = this.clazz.getSimpleName().toLowerCase() + "." + System.currentTimeMillis() + ".xlsx";
        this.excelTitle = excelTitle;
        return export(fields, dataList, excelTitle);
    }

    /**
     * 创建列头单元格
     */
    public Cell createCell(Row row, int column, XSSFRichTextString text) {
        // 创建列
        Cell cell = row.createCell(column);
        // 写入列信息
        cell.setCellValue(text);
        cell.setCellStyle(styles.get("header"));
        return cell;
    }

    /**
     * 创建单元格
     *
     * @param row
     * @param column
     * @param value
     */
    private void addCell(Row row, int column, Object value) {
        Cell cell = null;
        try {
            //Pattern p = Pattern.compile("[0-9]*");
            //Matcher matcher = p.matcher(value.toString());
            cell = row.createCell(column);

            /*if (matcher.matches() && !"".equals(value.toString())) {
                cell.setCellType(CellType.NUMERIC);
                cell.setCellValue(Double.parseDouble(value.toString()));
            } else if */
            String cellValue;
            if (value == null) {
                cellValue = "";
            } else if (value instanceof Date) {
                cellValue = new SimpleDateFormat(DateUtils.YYYY_MM_DD_HH_MM_SS).format(value);
            } else if (value instanceof LocalDateTime) {
                cellValue = DateTimeFormatter.ofPattern(DateUtils.YYYY_MM_DD_HH_MM_SS).format((LocalDateTime) value);
            } else {
                cellValue = value.toString();
            }
            cell.setCellValue(cellValue);

            // 设置行高
            row.setHeight((short) (15 * 20));
            cell.setCellStyle(styles.get("data"));
        } catch (Exception e) {
            log.error("导出失败", e);
        }

    }

    /**
     * 读取excel表
     *
     * @param sheetName   工作表名 如不指定传入 ""
     * @param excelColumn 需要导入的列字段 数组
     * @param is
     * @return
     * @throws Exception
     */
    public List<T> importExcel(String sheetName, String[] excelColumn, InputStream is) throws Exception {
        this.type = Excel.Type.IMPORT;
        this.workbook = WorkbookFactory.create(is);
        List<T> list = new ArrayList<>();

        Sheet sheet = null;
        if (StringUtils.isNotEmpty(sheetName)) {
            // 如果指定sheet名,则取指定sheet中的内容.
            sheet = workbook.getSheet(sheetName);
        } else {
            // 如果传入的sheet名不存在则默认指向第1个sheet.
            sheet = workbook.getSheetAt(0);
        }

        if (sheet == null) {
            throw new IOException("文件sheet不存在");
        }

        //sheet行数
        int rows = sheet.getPhysicalNumberOfRows();
        if (rows > 0) {
            for (int i = 0; i < rows - 1; i++) {
                //默认第一行是表头，第二行开始取数据
                Row row = sheet.getRow(i + 1);
                T entity = null;

                entity = (entity == null ? clazz.newInstance() : entity);
                for (int j = 0; j < excelColumn.length; j++) {
                    Object cellValue = getCellValue(row, j);
                    getValue(entity, excelColumn[j], cellValue);
                }
                list.add(entity);
            }
        }

        return list;
    }

    /**
     * 获取单元格值
     *
     * @param row    获取的行
     * @param column 获取单元格列号
     * @return 单元格值
     */
    public Object getCellValue(Row row, int column) {
        if (row == null) {
            return row;
        }
        Object val = "";
        SimpleDateFormat sdf = new SimpleDateFormat(DateUtils.YYYY_MM_DD_HH_MM_SS);

        try {
            Cell cell = row.getCell(column);
            if (StringUtils.isNotNull(cell)) {
                if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA) {
                    val = cell.getNumericCellValue();
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                    } else {
                        if ((Double) val % 1 > 0) {
                            val = new DecimalFormat("0.00").format(val);
                        } else {
                            val = new DecimalFormat("0").format(val);
                        }
                    }
                } else if (cell.getCellTypeEnum() == CellType.STRING) {
                    val = cell.getStringCellValue();
                } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
                    val = cell.getBooleanCellValue();
                } else if (cell.getCellTypeEnum() == CellType.ERROR) {
                    val = cell.getErrorCellValue();
                }

            }
        } catch (Exception e) {
            return val;
        }
        return val;
    }

    /**
     * 编码文件名
     */
    public String encodingFilename(String filename)
    {
        filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
        return filename;
    }

    /**
     * 获取下载路径
     *
     * @param filename 文件名称
     */
    public String getAbsoluteFile(String filename)
    {
        String downloadPath = RuoYiConfig.getDownloadPath() + filename;
        File desc = new File(downloadPath);
        if (!desc.getParentFile().exists())
        {
            desc.getParentFile().mkdirs();
        }
        return downloadPath;
    }

    /**
     * 创建一个工作簿
     */
    public void createWorkbook() {
        this.workbook = new SXSSFWorkbook(500);
    }

    /**
     * 创建工作表
     *
     * @param sheetNo sheet数量
     * @param index   序号
     */
    public void createSheet(double sheetNo, int index) {
        this.sheet = workbook.createSheet("sheet" + index);
        this.styles = createStyles(workbook);
        // 设置工作表的名称.
        if (sheetNo == 0) {
            workbook.setSheetName(index, sheetName);
        } else {
            workbook.setSheetName(index, sheetName + index);
        }
    }

    /**
     * 以类的属性的get set方法方法形式获取值
     *
     * @param o
     * @param name
     * @return value
     * @throws Exception
     */
    private Object getValue(Object o, String name, Object val) throws InvocationTargetException, IllegalAccessException {
        try {
            if (StringUtils.isNotEmpty(name)) {
                Class clazz = o.getClass();
                Method method = null;
                String methodName = "";


                if (type.value() == 1) {
                    //导出
                    methodName = GETTER_PREFIX + StringUtils.capitalize(name);
                    method = clazz.getMethod(methodName);
                    o = method.invoke(o);
                } else {
                    methodName = SETTER_PREFIX + StringUtils.capitalize(name);
                    Field field = null;

                    field = clazz.getDeclaredField(name);
                    Class<?> fieldType = field.getType();
                    if (String.class == field.getType()) {
                        val = Convert.toStr(val);
                    } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
                        val = Convert.toInt(val);
                    } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
                        val = Convert.toLong(val);
                    } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
                        val = Convert.toDouble(val);
                    } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
                        val = Convert.toFloat(val);
                    } else if (BigDecimal.class == fieldType) {
                        val = Convert.toBigDecimal(val);
                    }

                    if (!val.equals("") && val != null) {
                        method = clazz.getMethod(methodName, val.getClass());
                        o = method.invoke(o, val);
                    }
                }
            }
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
            throw new BusinessException("请核对字段");
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
            throw new BusinessException("请核对字段");
        }
        return o;
    }


    /**
     * 创建表格样式
     *
     * @param wb 工作薄对象
     * @return 样式列表
     */
    private Map<String, CellStyle> createStyles(Workbook wb) {
        // 写入各条记录,每条记录对应excel表中的一行
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.BLACK1.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);
        return styles;
    }
}
